package com.imooc.jdbc.hrapp.command;

import java.sql.*;
import java.util.Scanner;

public class QueryCommand implements Command{

    @Override
    public void execute() {

        // 1.加载并注册JDBC驱动
        // 2.创建数据库连接
        // 3. 创建Statement对象
        // 4.遍历查询结果
        // 5. 关闭连接，释放资源

        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        System.out.println("请输入部门名称：");
        Scanner in = new Scanner(System.in);

        String dep_name = in.next();

        try {
            String drive = "com.mysql.cj.jdbc.Driver";
            Class.forName(drive);
            String url = "jdbc:mysql://192.168.7.190:3306/yulei_test?useSSL=false&characterEncoding=UTF-8&useUnicode=true";
            String dbUser = "testreadwrite";
            String dbPwd = "testrw190!@#";

            conn = DriverManager.getConnection(url,dbUser, dbPwd);

            /**
             * 使用Statement可能会发生SQL注入攻击
             * 通过使用PreparedStatemnet进行SQL预处理操作
             */
            stmt = conn.createStatement();
            rs = stmt.executeQuery("select * from employee where dep_name = '"+ dep_name +"'");

            while (rs.next()){
                Integer id = rs.getInt(1);
                String dep = rs.getString("dep_name");
                Float salary = rs.getFloat("salary");
                String ename = rs.getString("emp_name");

                System.out.println(id + "-" +dep + "-" + ename + "-" + salary);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {

            try {
                if(rs !=null){
                    rs.close();
                }

                if(stmt !=null){
                    stmt.close();
                }

                if(conn != null && !conn.isClosed()){
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }

    }
}
